package com.wny.server;

import java.util.ArrayList;
import java.util.ListIterator;

/**
 * @author Li Supply a constructor, a hasEntity() and a dbClose() methods, a
 *         dbSelect() and a dbUpdate() methods.
 */

public class DBOperatorWrapper implements DBOWrapperInterface {

	DBOperator dbOperator = null;

	public DBOperatorWrapper(String host, String port, String dName,
			String uName, String pwd) {
		dbOperator = new DBOperator(host, port, dName, uName, pwd);
	}

	public boolean Connect() {
		return dbOperator.dbConnect();
	}
 
	public boolean Close() {
		return dbOperator.dbClose();
	}

	public boolean hasEntity(String table, String[] arg) {
		StringBuilder sql = new StringBuilder("select * from " + table);

		// if it has filter
		if (arg.length > 1) {
			sql.append(" where ");
			for (int i = 1; i < arg.length; i++) {
				String[] args = arg[i].split("=");
				sql.append(args[0] + "=");
				sql.append("'" + args[1] + "' and ");
			}
			// replace the last " and " with ";"
			sql.replace(sql.length() - 5, sql.length(), ";");
		} else {
			sql.append(";");
		}
		ArrayList<String> results = dbOperator.dbSelect(sql.toString());
		return (results.size() > 0);
	}
	
	public void updateLocation(String lat, String lon, String uid)
	{
		StringBuilder sql = new StringBuilder("update user set latitude="+lat +", longitude=" + lon + " where uid =" + uid +";");
		dbOperator.dbUpdate(sql.toString());
	}

	public String insert(String table, String[] arg) {

		StringBuilder sql = new StringBuilder("insert into " + table);
		StringBuilder keys = new StringBuilder(" (");
		StringBuilder values = new StringBuilder(" values(");

		// arg[0] is the request header, no use here.
		for (int i = 1; i < arg.length; i++) {
			// e.g. arg[1] = "name=test1"
			String[] args = arg[i].split("=");
			// The
			keys.append(args[0] + ",");
			values.append("'" + args[1] + "',");
		}
		// replace the last ',' to ')'
		keys.replace(keys.length() - 1, keys.length(), ")");
		values.replace(values.length() - 1, values.length(), ");");

		sql.append(keys);
		sql.append(values);

		System.out.println(sql);

		boolean updateSuccess = dbOperator.dbUpdate(sql.toString());
		if (updateSuccess)
			return "1";
		return "0";
	}

	public String update(String table, String[] arg) {
		String uid = arg[1].split("=")[1];
		StringBuilder sql = new StringBuilder("select "
				+ Constant.PRIVACY_LEVEL_ROW + " from " + table);
		sql.append(" where uid=" + uid);
		ArrayList<String> result = dbOperator.dbSelect(sql.toString());
		if (result.size() == 0) {
			String[] newArgs = { arg[0], arg[1],
					Constant.PRIVACY_LEVEL_ROW + "=1" };
			insert(table, newArgs);
			return "public";
		}
		int oldVal = Integer.valueOf(result.get(0).split("/")[0]);
		sql = new StringBuilder("update " + table + " set "
				+ Constant.PRIVACY_LEVEL_ROW);
		if (oldVal == 0)
			sql.append("=1");
		else
			sql.append("=0");
		sql.append(" where uid=" + uid);
		dbOperator.dbUpdate(sql.toString());
		if (oldVal == 0)
			return "public";
		else
			return "private";
	}

	public String delete(String table, String[] arg) {

		StringBuilder sql = new StringBuilder("delete from ");
		String[] arg1, arg2;

		sql.append(table);
		sql.append(" where ");
		
		if (table.equals("friend")) {
			// get args that store every key and value separately for delete friendship
			arg1 = arg[1].split("=");
			arg2 = arg[2].split("=");
			
			//arg[1] = "uid1 = <uid1>"
			//arg[2] = "uid2 = <uid2>"
			sql.append("(uid1 = " + arg1[1] + " and uid2 = " + arg2[1] + ") or "); 
			sql.append("(uid1 = " + arg2[1] + " and uid2 = " + arg1[1] + ")");
		}
		else if(table.equals("eventList")) {
			// format: del=eventList&uid=<uid>&eid=<eid>
			arg1 = arg[1].split("=");
			arg2 = arg[2].split("=");
			
			sql.append("uid = " + arg1[1] + " and eid = " + arg2[1]);
		}
		else {
			// arg[1] = "uid = <uid>" or "uid1 = <uid1>" 
			sql.append(arg[1]);			
		}
		boolean updateSuccess = dbOperator.dbUpdate(sql.toString());
		if (updateSuccess)
			return "1";
		return "0";
	}
  
	public String retrieve(String table, String[] arg) {

		StringBuilder sql;
		
		// To get whether this query is get=user&eventList.eid=<eid>&uid=<uid>
		boolean hasELeid = false; 
		
		if (arg.length > 2) { 
			String[] ELeid = arg[1].split("=");
			if(ELeid[0].equals("eventList.eid") & table.equals("user"))
				hasELeid = true;
		}
		// end
		
		if (table.equals("eventList")) {
			// format1: get=eventList&uid=<uid>
			// format2: get=eventList&uid=<uid>&eid=<eid>
			String[] arg1 = arg[1].split("=");

			if(arg.length == 2) {	// format1
				sql = new StringBuilder("select e.* from eventList el, event e where el.uid = " + arg1[1] + " and el.eid = e.eid");
			}
			else {	// format2
				String[] arg2 = arg[2].split("=");
				sql = new StringBuilder("select eid from eventList where uid = " + arg1[1] + " and eid = " + arg2[1]);
			}
			
			
		} else if (table.equals("friend")) {
			// format: get=friend&uid1=<uid> or get=friend&uid1=<uid>&uid2=<uid2> 
			
			String[] arg1 = arg[1].split("=");
			
			if (arg.length == 2) {
				sql = new StringBuilder(
					//"select u.uid from user u, friend f where f.uid1 = "
					"select * from user u, friend f where " +
					"(f.uid1 ="	+ arg1[1] + " and f.uid2 = u.uid) or " + 
					"(f.uid1 = u.uid and f.uid2 = " + arg1[1] + ")");
			}
			else {
				String[] arg2 = arg[2].split("=");
				sql = new StringBuilder(
					"select uid1 from friend where" + 
					"(uid1 = " + arg1[1] + " and uid2 = " + arg2[1]	+ ") or " + 
					"(uid2 = " + arg1[1] + " and uid1 = " + arg2[1] + ")");
			}
			
		} else if (hasELeid) {
			// to get all user joins the event except current user. (don't have to list himself)
			// format: get=user&eventList.eid=<eid>&uid=<uid>

			String[] arg1 = arg[1].split("=");
			String[] arg2 = arg[2].split("=");
			sql = new StringBuilder(
					"select u.* from user u, eventList el where el.eid = "
							+ arg1[1] + " and el.uid = u.uid and u.uid <> " + arg2[1]);			
		}
		  else {
			sql = new StringBuilder("select * from " + table);
			
			// if it has filer
			if (arg.length > 1) {
				sql.append(" where ");
				String[] isLoc = arg[1].split("="); // location should be the
													// first value if specified
				if (isLoc[0].equals("location")) {  // if location is specified
													// in the filter

					String[] locParameters = isLoc[1].split("#");

					String lat = locParameters[0];
					String log = locParameters[1];
					String rad = locParameters[2];

					sql.append("sqrt(pow((" + table + ".latitude-" + lat
							+ "),2)+pow((" + table + ".longitude-" + log
							+ "),2)) <= " + rad + " and ");

					// when there are other parameters other than location
					for (int i = 2; i < arg.length; i++) {
						String[] args = arg[i].split("=");
						sql.append(args[0] + "=");
						sql.append("'" + args[1] + "' and ");
					}
					// replace the last " and " with ";"
					sql.replace(sql.length() - 5, sql.length(), ";");

				} else { // if location is not specified in the filter
					
					for (int i = 1; i < arg.length; i++) {
						String[] args = arg[i].split("=");
						sql.append(args[0] + "=");
						sql.append("'" + args[1] + "' and ");
					}
					// replace the last " and " with ";"
					sql.replace(sql.length() - 5, sql.length(), ";");
				}

			} else { // if it doesn't have a filter
				if(table.equals("user")) {
					sql = new StringBuilder("select u.* from user u, privacy p where p.private = 1 and p.uid = u.uid");
				}
			}
		}

		System.out.println(sql);

		return arrayListToString(dbOperator.dbSelect(sql.toString()));
	}

	public String arrayListToString(ArrayList<String> list) {

		StringBuilder toReturn = new StringBuilder();
		ListIterator<String> it = list.listIterator();

		while (it.hasNext()) {
			toReturn.append(it.next());
			toReturn.append(Constant.RESULT_DELIM_ENTITY);
		}
		return toReturn.toString();
	}

	public String otheract(String table, String[] arg) {

		if (table.equals("log")) {

			StringBuilder sql = new StringBuilder(
					"select pwd from user where name = ");
			String[] args = arg[1].split("=");
			sql.append("'" + args[1] + "' and pwd = ");
			args = arg[2].split("=");
			sql.append("'" + args[1] + "'");
			ArrayList<String> result = dbOperator.dbSelect(sql.toString());

			if (result.size() > 0)
				return "1";
			else
				return "0";

		} else {
			return "";
		}
	}

	public String getid(String table, String[] keyVals) {
		// TODO Auto-generated method stub
		StringBuilder sql = new StringBuilder("select uid from " + table);

		if (keyVals.length > 1) {

			sql.append(" where ");

			for (int i = 1; i < keyVals.length; i++) {

				String[] args = keyVals[i].split("=");
				sql.append(args[0] + "=");
				sql.append("'" + args[1] + "' and ");
			}
			// replace the last " and " with ";"
			sql.replace(sql.length() - 5, sql.length(), ";");

		} else {
			return null;
		}
		return dbOperator.dbSelect(sql.toString()).get(0);
	}

}
